package com.woniuxy.shop.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import com.woniuxy.shop.entity.Goods;
import com.woniuxy.shop.entity.Order;

/**
*@author 作者：
*version 创建时间：
*类说明：
*/
public class GoodsDao {
	
	/**
	 * 增加商品信息
	 * @param g
	 */
	public void add(Goods g) {
		String sql = "insert into shop_goods(name,categoryid,description,image,marketprice,saleprice,score,stock,status) values(?,?,?,?,?,?,?,?,?)";
		Object[] obs = {g.getName(),g.getCategoryid(),g.getDescription(),g.getImage(),g.getMarketprice(),g.getSaleprice(),g.getScore(),g.getStock(),g.getStatus()};
		DbHelper.excuteSQL(sql, obs);
	}
	
	/**
	 * 指定ID删除商品信息
	 * @param id
	 */
	public void delete(int id) {
		String sql = "delete from shop_goods where id=?";
		DbHelper.excuteSQL(sql,id);
	}
	
	/**
	 * 更新商品所有信息
	 * @param g
	 */
	public void updateAll(Goods g) {
		String sql = "update shop_goods set name=?,categoryid=?,description=?,image=?,marketprice=?,saleprice=?,score=?,stock=?,status=? where id=?";
		Object[] obs = {g.getName(),g.getCategoryid(),g.getDescription(),g.getImage(),g.getMarketprice(),g.getSaleprice(),g.getScore(),g.getStock(),g.getStatus(),g.getId()};
		DbHelper.excuteSQL(sql, obs);
	}
	
	/**
	 * 更新指定ID的库存
	 * @param stock
	 * @param id
	 */
	public void updateStock(int stock,int id) {
		String sql = "update shop_goods set stock=? where id=?";
		DbHelper.excuteSQL(sql, stock, id);
	}
	
	/**
	 * 分页查询指定页的商品信息
	 * @param pagesize
	 * @param page
	 * @return
	 */
	public List<Goods> getAllGoods(int pagesize,int page){
		List<Goods> allGoods = new ArrayList<>();
		String sql = "select * from shop_goods limit ?,?";
		allGoods = DbHelper.executeQuery(sql,Goods.class,(page-1)*pagesize,pagesize);
		return allGoods;
	}
	
	
	/**
	 * 查询指定ID的商品所有信息
	 * @param id
	 * @return
	 */
	public Goods getGoodsByID(int id) {
		String sql = "select * from shop_goods where id=?";
		return DbHelper.executeQueryForSingleRow(sql, Goods.class, id);
	}
	
	/**
	 * 查询指定ID的商品所有信息
	 * @param id
	 * @return
	 */
	public Goods getGoodsByName(String name) {
		String sql = "select * from shop_goods where name=?";
		return DbHelper.executeQueryForSingleRow(sql, Goods.class, name);
	}
	
	/**
	 * 查询总共的商品数量
	 * @return
	 */
	public int getTotalGoodsNum() {
		String sql = "select count(*) from shop_goods";
		return ((Long)DbHelper.executeQueryForScalar(sql)).intValue();
	}
	
	/**
	 * 查询指定商品ID的库存
	 * @param id
	 * @return
	 */
	public int getStockByID(int id) {
		String sql = "select stock from shop_goods where id=?";
		return DbHelper.executeQueryForScalar(sql, id);
	}
	
	/**
	 * 查询指定商品ID的价格
	 * @param id
	 * @return
	 */
	public double getPriceByID(int id) {
		String sql = "select saleprice from shop_goods where id=?";
		return ((BigDecimal)DbHelper.executeQueryForScalar(sql, id)).doubleValue();
	}

	
	/**
	 * 多条件查询商品数量
	 * @param g
	 * @return
	 */
	public int getGoodsAsLimit(Goods g) {
		String sql = "select count(*) from shop_goods g where 1=1";
		if(g.getName()!=null&&!g.getName().trim().equals("")) {
			//按名字查询
			sql += " and g.name like'%"+g.getName()+"%'";
		}
		if(g.getDescription()!=null&&!g.getDescription().trim().equals("")) {
			//按描述查询
			sql += " and description like'%"+g.getDescription()+"%'";
		}
		if(g.getStatus()!=null&&!g.getStatus().trim().equals("")) {
			//按状态查询
			sql += " and g.status ='"+g.getStatus()+"'";
		}
		if(g.getCategoryid()!=0) {
			//按类别查询
			sql += " and categoryid="+g.getCategoryid();
		}
		if(g.getMinprice()!=0) {
			sql += " and saleprice>=" +g.getMinprice();
		}
		if(g.getMaxprice()!=0) {
			sql += " and saleprice<=" +g.getMaxprice();
		}
		if(g.getMinstock()!=0) {
			sql += " and stock>=" +g.getMinstock();
		}
		if(g.getMaxstock()!=0) {
			sql += " and stock<=" +g.getMaxstock();
		}
		return ((Long)DbHelper.executeQueryForScalar(sql)).intValue();
	}
	
	/**
	 * 多条件分页查询商品信息
	 * @param page
	 * @param pagesize
	 * @param g
	 * @return
	 */
	public List<Goods> getGoodsByPage(int page, int pagesize, Goods g) {
		String sql = "select g.*,c.name cname from shop_goods g,shop_category c where g.categoryid=c.id";
		if(g.getName()!=null&&!g.getName().trim().equals("")) {
			//按名字查询
			sql += " and g.name like'%"+g.getName()+"%'";
		}
		if(g.getDescription()!=null&&!g.getDescription().trim().equals("")) {
			//按描述查询
			sql += " and description like'%"+g.getDescription()+"%'";
		}
		if(g.getStatus()!=null&&!g.getStatus().trim().equals("")) {
			//按状态查询
			sql += " and g.status ='"+g.getStatus()+"'";
		}
		if(g.getCategoryid()!=0) {
			//按类别查询
			sql += " and categoryid="+g.getCategoryid();
		}
		if(g.getMinprice()!=0) {
			sql += " and saleprice>=" +g.getMinprice();
		}
		if(g.getMaxprice()!=0) {
			sql += " and saleprice<=" +g.getMaxprice();
		}
		if(g.getMinstock()!=0) {
			sql += " and stock>=" +g.getMinstock();
		}
		if(g.getMaxstock()!=0) {
			sql += " and stock<=" +g.getMaxstock();
		}
		sql += " limit ?,?";
		return DbHelper.executeQuery(sql, Goods.class, (page-1)*pagesize,pagesize);
	}
	
	/**
	 * 根据ID查询商品
	 * @param categoryid
	 * @return
	 */
	public List<Goods> queryGoodsByCateGoryId(int categoryid){
		String sql = "select * from shop_goods where categoryid=?";
		return DbHelper.executeQuery(sql, Goods.class, categoryid);
				
	}
	
	/**
	 * 更改指定ID的状态
	 * @param id
	 * @param status
	 */
	public void updateStatus(int id,String status) {
		String sql = "update shop_goods set status=? where id=?";
		DbHelper.excuteSQL(sql, status,id);
	}
	
	/**
	 * 根据是否是热卖商品进行查询
	 * @param attr
	 * @return
	 */
	public List<Goods> getHotGoods(String attr){
		String sql = "select * from shop_goods where hotGoods= ?";
		return DbHelper.executeQuery(sql, Goods.class, attr);
	}
	
	/**
	 * 根据是否是最新商品进行查询
	 * @param attr
	 * @return
	 */
	public List<Goods> getNewGoods(String attr){
		String sql = "select * from shop_goods where newGoods= ?";
		return DbHelper.executeQuery(sql, Goods.class, attr);
	}
	
	/**
	 * 查询指定类别的商品
	 * @param catagoryid
	 * @return
	 */
	public List<Goods> getGoodsByCateGory(int catagoryid) {
		String sql = "select * from shop_goods where categoryid=?";
		return DbHelper.executeQuery(sql,Goods.class,catagoryid);
		
	}
	
	
}
